from utils.sql_utils import SQLManager
class ShopService:
    def get_goods_list(goods_id):
        sql = """
            SELECT 
                g.id AS goods_id,
                g.name AS goods_name,
                g.description AS goods_description,
                g.image_url AS goods_image_url,
                g.weight AS goods_weight,
                g.length AS goods_length,
                g.width AS goods_width,
                g.height AS goods_height,
                s.sku_no AS sku_number,
                s.sku_price AS sku_price,
                s.specification AS sku_specification,
                s.stock_quantity AS sku_stock_quantity,
                s.lock_quantity AS sku_lock_quantity,
                s.remaining_quantity AS sku_remaining_quantity,
                sl.seller_name AS seller_name,
                sl.avatar_url AS seller_avatar_url,
                i.slide_img_url AS shop_slide_image_url
            FROM 
                rb_shop_goods g
            LEFT JOIN 
                rb_shop_sku s ON g.unique_id = s.goods_id
            LEFT JOIN 
                rb_shop_seller sl ON g.seller_id = sl.unique_id
            LEFT JOIN 
                rb_shop_slide_imgs i ON g.unique_id = i.goods_id
            WHERE 
                g.unique_id = %s;
        """
        values = (goods_id,)
        res = SQLManager().get_list(sql,values)

        return res
    
    def get_orders_by_userId(user_unique_id):
        sql = """
            SELECT 
                o.id AS order_id,
                o.user_unique_id,
                o.goods_unique_id,
                g.name AS goods_name,
                o.total_price,
                o.status,
                o.quantity,
                o.created_at,
                o.is_deleted
            FROM 
                rb_shop_orders o
            LEFT JOIN 
                rb_shop_goods g ON o.goods_unique_id = g.unique_id
            WHERE 
                o.user_unique_id = %s
                AND o.is_deleted = 0;
        """

        values = (user_unique_id,)
        res = SQLManager().get_list(sql,values)

        return res
    
    def get_carts_by_userId(user_unique_id):
        sql = """
            SELECT 
                c.id AS cart_id,
                c.user_unique_id,
                c.goods_unique_id,
                g.name AS goods_name,
                g.description,
                c.quantity,
                c.created_at
            FROM 
                rb_shop_cart c
            LEFT JOIN 
                rb_shop_goods g ON c.goods_unique_id = g.unique_id
            WHERE 
                c.user_unique_id = %s
                AND c.is_deleted = 0;
        """

        values = (user_unique_id,)
        res = SQLManager().get_list(sql,values)

        return res
    

    def get_order_by_goodid_userId(goods_unique_id, user_unique_id):
        
        sql = """
            SELECT 
                o.id AS order_id,
                o.unique_id AS order_unique_id,
                o.goods_unique_id AS order_goods_unique_id,
                o.total_price AS order_total_price,
                o.status AS order_status,
                o.created_at AS order_created_at,
                o.quantity AS order_quantity,
                o.price AS order_price,
                o.seller_id AS order_seller_id,
                o.is_deleted AS order_is_deleted,
                o.modify_time AS order_modify_time
            FROM 
                rb_shop_orders AS o
            WHERE 
                o.user_unique_id = %s -- 用户ID
                AND o.goods_unique_id = %s -- 商品ID
                AND o.is_deleted = 0;
        """

        values = (goods_unique_id,user_unique_id)
        res = SQLManager().get_list(sql,values)

        return res